<?php

namespace App\Http\Controllers\Admin\ExportReport;

use Dompdf\Exception;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpParser\Node\Stmt\DeclareDeclare;

class ExportReportShanDongLinYiController extends Controller
{

    /* 导出Excel 检测报告*/
    public function export(Request $request) {

        $contract_id = $request->id; //批次 id
        //查询当前批次的信息
        $data_batch = DB::table('a_contract')->find($contract_id);
        $batchNum = $data_batch->batchNum;

        $storeFile = 'reports/'.$batchNum.'.xls';
        $disk = Storage::disk('public');
        if($disk->exists($storeFile) ){
            if($data_batch->status < 14){
                $disk->delete($storeFile);
            }else{
                return $disk->download($storeFile);
            }
        }
        //================================================== 封面 start ============================================
        $template_file = './storage/ExcelTemplate/封面.xls'; //考虑到兼容Linux，斜杠用“/”
        if(!file_exists($template_file)) {
            return '封面模板文件不存在';
        }

        //        if (isset(request()->toPdf)) {
        //            $pdfpath = 'reportTemp/' . $data_batch->batchNum . ".pdf";
        //            if (file_exists($pdfpath)) {
        //                header('location:' . request()->root() . '/' . $pdfpath);
        //                exit();
        //            }
        //        }

        //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
        $report_num_pre = DB::table('setting')->where('id', '1')->value('report_num_pre'); //报告编号前缀
        $company_name = DB::table('setting')->where('id', '1')->value('name_rep'); //获取检测公司名称
        $headerContent = "&R{$company_name}     {$report_num_pre}{$data_batch->batchNum}"; //眉头内容
        $headerPageMargins = 0.4; //眉头的页边距

        $footerContent = '&R&12第 &P 页，共 &N 页'; //页脚内容

        $footerPageMargins = 0.1; //页脚的页边距

        $bottomPageMargins = 0.3; //下边的页边距
        //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取模板文件
        $worksheet = $spreadsheet->getSheet(0); //按索引获取表单，从 0 开始
        $worksheet->setTitle('封面'); //重命名工作表
        $worksheet->getHeaderFooter()->setOddFooter($footerContent); //页脚  Odd
        $worksheet->getHeaderFooter()->setEvenFooter($footerContent); //页脚  Even
        $worksheet->getPageMargins()->setFooter($footerPageMargins); //设置页脚的页边距

        $worksheet->getPageMargins()->setBottom($bottomPageMargins); //设置下边的页边距


        $worksheet->getCell('X22')->setValue($report_num_pre.$data_batch->batchNum); //报告编号前缀 + 报告编号
        $worksheet->getCell('P27')->setValue($data_batch->client_name); //送检单位
        $worksheet->getCell('P25')->setValue($data_batch->entrusted_company); //委托单位
        $worksheet->getCell('P26')->setValue($data_batch->client_addr); //地址
        $worksheet->getCell('P28')->setValue($data_batch->test_kind); //检测性质
        // $worksheet->getCell('P27')->setValue($data_batch->client_name); //受检单位
        //==================================================== 封面 end ===================================================

        //================================================== 封底 start ============================================
        $template_file = './storage/ExcelTemplate/封底.xls'; //考虑到兼容Linux，斜杠用“/”
        if(file_exists($template_file)) {
            $spreadsheet_common = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取模板文件
            $worksheet_common = $spreadsheet_common->getSheet(0); //按索引获取表单，从 0 开始
            $worksheet_common->setTitle('封底'); //重命名工作表
            $headerContentFD = "&L{$company_name}     {$report_num_pre}{$data_batch->batchNum}"; //眉头内容
            $footerContentFD = '&L&12第 &P 页，共 &N 页'; //页脚内容

            $worksheet_common->getHeaderFooter()->setOddHeader($headerContentFD); //检测公司名称 + 报告编号前缀 + 报告编号(封底页眉头)  Odd
            $worksheet_common->getHeaderFooter()->setEvenHeader($headerContentFD); //检测公司名称 + 报告编号前缀 + 报告编号(封底页眉头) Even
            $worksheet_common->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

            $worksheet_common->getHeaderFooter()->setOddFooter($footerContentFD); //页脚  Odd
            $worksheet_common->getHeaderFooter()->setEvenFooter($footerContentFD); //页脚  Even
            $worksheet_common->getPageMargins()->setFooter($footerPageMargins); //设置页脚的页边距

            $worksheet_common->getPageMargins()->setBottom($bottomPageMargins); //设置下边的页边距

            $spreadsheet->addExternalSheet($worksheet_common); //添加外部工作表
            //为了防止内存泄漏，建议用完手动清理
            $spreadsheet_common->disconnectWorksheets();
            unset($spreadsheet_common);
        }
        //================================================== 封底 end ============================================

        $sql = "SELECT DISTINCT
                    A.std_id,
                    A.tool_name,
                    A.tool_id,
                    A.unit,
                    A.std_num
                FROM
                    a_contract_gaiyao AS A
                WHERE
                    A.contract_id = :contract_id";
        //查询出当前批次下 标准、工器具的集合，并去重
        $data_std_tool = DB::select($sql, ['contract_id' => $contract_id]);
        foreach($data_std_tool as $k => $v) {
            # 查询数据的位置信息，即数据要存到Excel的哪个单元格
            $info_pos = DB::table('a_excel_pos as B')->leftJoin('a_std_tool_rel as A', 'A.id', '=', 'B.std_tool_rel_id')
                          ->where(['A.std_id' => $v->std_id, 'A.tool_id' => $v->tool_id, 'B.is_default' => 1])->select('B.*', 'A.cycle', 'A.ctime', 'A.remark')->first();
            if(!$info_pos) {
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet->disconnectWorksheets();
                unset($spreadsheet);

                return "{$v->tool_name} 未指定数据在模板文件中的位置";
            }
            $excel_pos_detail = [];
            if($info_pos->name !== '默认') {
                $excel_pos_detail_info = DB::table('a_excel_pos_detail')->where('excel_pos_id', $info_pos->id)->value('info');
                $excel_pos_detail = json_decode($excel_pos_detail_info, true);
            }
            $posname = $info_pos->name === '默认' ? '' : $info_pos->name;
            # 检测模版文件是否存在
            $template_file = "./storage/ExcelTemplate/{$v->std_id}/{$v->tool_name}{$posname}.xls"; //考虑到兼容Linux，斜杠用“/”
            if(!file_exists($template_file)) {
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet->disconnectWorksheets();
                unset($spreadsheet);
                return "{$v->tool_name}的模板文件不存在";
            }

            //未指定行号
            if($info_pos->line_num === '' || intval($info_pos->line_num) < 1) {
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet->disconnectWorksheets();
                unset($spreadsheet);
                return "{$v->tool_name} 未指定数据在模板文件中的行号";
            }

            try {
                //==========================去具体工器具模版文件 复制表单 到 封面模板文件========================
                $spreadsheet2 = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取被复制的模板文件
                // $worksheet2 = $spreadsheet2->getSheet(0); //按索引获取被复制的表单，从 0 开始
                $worksheet2 = $spreadsheet2->getActiveSheet(); //在MS Excel（或其他适当的Spreadsheet程序）中打开工作簿时将处于活动状态的工作表
                $worksheet2->setTitle("HelloWorld-gww{$k}");
                // $worksheet3 = clone $worksheet2; //按索引获取被复制的表单，从 0 开始
                $spreadsheet->addExternalSheet($worksheet2); //添加外部工作表
                // $objClonedWorksheet = clone $objPHPExcel1->getSheetByName('Worksheet 1');
                // $objPHPExcel->addExternalSheet($objClonedWorksheet);

                //getSheetCount()方法将告诉您工作簿中的工作表数量,class Spreadsheet 的方法
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet2->disconnectWorksheets();
                unset($spreadsheet2);
                //=========================================================================================
                $worksheet = $spreadsheet->getSheetByName("HelloWorld-gww{$k}"); //按表单名 获取到激活的表单
            } catch (Exception $e) {
                dd($e->getMessage());
            }

            // if (isAdmin() && $k == 9) {
            //     break;
            //     // code...
            // }
            //设置内容适应页面(显示在一页里)
            //            $worksheet->getPageSetup()->setFitToPage(true);

            $worksheet->setTitle("{$v->tool_name}{$v->std_id}");

            
            // $worksheet->getPageSetup()->setPrintArea('A1:BA1000');//指定打印区域

            /* if ($info_pos->company !== '') {
                $company_name = DB::table('setting')->where('id', '1')->value('name_rep');//获取检测公司名称
                $worksheet->getCell($info_pos->company)->setValue($company_name); //检测公司名称
            }
            if ($info_pos->report_num !== '') {
                $worksheet->getCell($info_pos->report_num)->setValue($report_num_pre . $data_batch->batchNum); //报告编号前缀 + 报告编号
            } */
            // if(!$worksheet) dd($worksheet);//---------------------------------------------------------------------------------------------------------------------------????
            // if(!$worksheet) dd("{$v->tool_name}{$v->std_id}");//-------------------------------------------------------------------------------------------------------??????
            $worksheet->getHeaderFooter()->setOddHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(检测报告正文页眉头)  Odd
            $worksheet->getHeaderFooter()->setEvenHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(检测报告正文页眉头) Even
            $worksheet->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

            $worksheet->getHeaderFooter()->setOddFooter($footerContent); //页脚  Odd
            $worksheet->getHeaderFooter()->setEvenFooter($footerContent); //页脚  Even
            $worksheet->getPageMargins()->setFooter($footerPageMargins); //设置页脚的页边距

            $worksheet->getPageMargins()->setBottom($bottomPageMargins); //设置下边的页边距

            $DY_RiQi = strtotime($data_batch->created_at); //将表示时间和日期的字符串转化为相应的时间戳
            if($info_pos->come_date !== '') {
                $worksheet->getCell($info_pos->come_date)->setValue(date("Y年m月d日", $DY_RiQi)); //到样日期
            }
            //------------------------------------------检测日期---------
            //检验日期开始时间
            $testdate_start = DB::table('a_item_data as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->min('utime');
            if($testdate_start) {
                $testdate_start = strtotime($testdate_start); //将任何字符串的日期时间描述解析为 Unix 时间戳
                $testdate_start = date("Y年m月d日", $testdate_start);
            }
            //检验日期结束时间
            $testdate_end = DB::table('a_sample_info as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->max('test_time');
            if($testdate_end) {
                $testdate_end = strtotime($testdate_end); //将任何字符串的日期时间描述解析为 Unix 时间戳
                $testdate_end = date("Y年m月d日", $testdate_end);
            }
            if($testdate_start === $testdate_end) {
                # 开始日期与结束日期相同
                $test_date = $testdate_start;
            }
            else {
                # code...
                $test_date = "{$testdate_start}～{$testdate_end}";
                // if ($info_pos->test_date !== '') {
                // $worksheet->getStyle($info_pos->test_date)->getFont()->setSize(9); //检测日期，字号设为 9
                // }
            }
            if($info_pos->test_date !== '') {
                $worksheet->getCell($info_pos->test_date)->setValue($test_date); //检测日期
            }
            //-------------------------------------------------------------------
            $sample_count = DB::table('a_contract_gaiyao')->where('contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->sum('count');
            if($info_pos->sample_count !== '') {
                $worksheet->getCell($info_pos->sample_count)->setValue($sample_count.$v->unit); //样品数量
            }

            $temp_hum_pressure_data = DB::table('a_item_data as B')->select('temp', 'hum', 'pressure')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('item_id', '!=', 61)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->where('res', '<', 2)->get();
            //            dd($temp_hum_pressure_data);
            // if ($k==1) dd($temp_hum_pressure_data);
  
            $se = ExportReportPublicController::tempHumPressureSE($temp_hum_pressure_data);
            list($temp, $hum, $pressure) = $se;
            //------------------------------------------ 温度 start---------
            if($info_pos->temp !== '') {
                $worksheet->getCell($info_pos->temp)->setValue($temp); //温度
            }
            //------------------------------------------ 温度 end ---------
            //------------------------------------------ 相对湿度 start---------
            if($info_pos->hum !== '') {
                $worksheet->getCell($info_pos->hum)->setValue($hum); //相对湿度
            }
            //------------------------------------------ 相对湿度 end ---------
            //------------------------------------------ 大气压力 start---------
            if($info_pos->pressure !== '') {
                $worksheet->getCell($info_pos->pressure)->setValue($pressure); //大气压力
            }
            //------------------------------------------ 大气压力 end ---------
            //------------------------------------------ 天气 start---------
            $wea = DB::table('a_item_data as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->where('wea', '!=', '')->value('wea');
            if($info_pos->wea !== '') {
                if($wea) {
                    $worksheet->getCell($info_pos->wea)->setValue($wea);
                } //天气
            }
            //------------------------------------------ 天气 end ---------

            $data_sample_info = DB::table('a_sample_info as B')->select('B.*', 'A.tool_name', 'A.voltage', 'A.class', 'A.std_id', 'A.tool_id', 'A.item_ids', 'A.vol_id', 'class_id')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->orderBy('B.bar_code', 'asc')->get();
            $rowIndex = $info_pos->line_num;
            $hege = $unhege = $weiZhi = [];
            $sampleMergeCells = $this->sampleMergeCells($worksheet,$info_pos); //样品内容合并样式
            $sample_rowspann = $info_pos->sample_rowspann;  //单个样品占用行数
            foreach($data_sample_info as $kk => $vv) {

                if($kk > 0) {
                    # 键>0 代表不是第一次循环
                    $worksheet->insertNewRowBefore($rowIndex, $sample_rowspann); //在第x行之前插入1个新行
                    $this->toMergeCells($kk,$info_pos,$sampleMergeCells,$worksheet);
                    $this->sampleDefVal($worksheet,$info_pos,$kk,$sampleMergeCells);

                    // # 键>0 代表不是第一次循环
                    // $worksheet->insertNewRowBefore($rowIndex, 1); //在第x行之前插入1个新行

                    // // $worksheet->mergeCells('A21:E21');//合并单元格
                    // if($info_pos->serial_num1 !== '' && $info_pos->serial_num2 !== '') {
                    //     $worksheet->mergeCells($info_pos->serial_num1.$rowIndex.':'.$info_pos->serial_num2.$rowIndex); //序号  合并单元格
                    // }
                    // if($info_pos->sample_num1 !== '' && $info_pos->sample_num2 !== '') {
                    //     $worksheet->mergeCells($info_pos->sample_num1.$rowIndex.':'.$info_pos->sample_num2.$rowIndex); //样品号  合并单元格
                    // }
                    // if($info_pos->self_num1 !== '' && $info_pos->self_num2 !== '') {
                    //     $worksheet->mergeCells($info_pos->self_num1.$rowIndex.':'.$info_pos->self_num2.$rowIndex); //自编号  合并单元格
                    // }
                    // if($info_pos->voltage1 !== '' && $info_pos->voltage2 !== '') {
                    //     $worksheet->mergeCells($info_pos->voltage1.$rowIndex.':'.$info_pos->voltage2.$rowIndex); //额定电压  合并单元格
                    // }
                    // if($info_pos->class1 !== '' && $info_pos->class2 !== '') {
                    //     $worksheet->mergeCells($info_pos->class1.$rowIndex.':'.$info_pos->class2.$rowIndex); //分类  合并单元格
                    // }
                    // if($info_pos->res1 !== '' && $info_pos->res2 !== '') {
                    //     $worksheet->mergeCells($info_pos->res1.$rowIndex.':'.$info_pos->res2.$rowIndex); //判定  合并单元格
                    // }
                    // if($info_pos->valid_date1 !== '' && $info_pos->valid_date2 !== '') {
                    //     $worksheet->mergeCells($info_pos->valid_date1.$rowIndex.':'.$info_pos->valid_date2.$rowIndex); //有效日期  合并单元格
                    // }
                }

                if($info_pos->serial_num1 !== '') {
                    $worksheet->getCell($info_pos->serial_num1.$rowIndex)->setValue($kk + 1); //序号
                }
                if($info_pos->sample_num1 !== '') {
                    $worksheet->getCell($info_pos->sample_num1.$rowIndex)->setValue(substr($vv->bar_code, 6).'#'); //样品号
                }
                if($info_pos->self_num1 !== '') {
                    $worksheet->getCell($info_pos->self_num1.$rowIndex)->setValue($vv->self_num); //自编号
                }
                if($info_pos->voltage1 !== '') {
                    $voltageWithoutKV = str_replace("kV", "", $vv->voltage); //将额定电压中的 kV去掉
                    $worksheet->getCell($info_pos->voltage1.$rowIndex)->setValue($voltageWithoutKV); //额定电压
                }
                if($info_pos->class1 !== '') {
                    $worksheet->getCell($info_pos->class1.$rowIndex)->setValue($vv->class); //分类
                }
                if($info_pos->res1 !== '') {
                    if($vv->res === 1) {
                        $hege[] = substr($vv->bar_code, 6);
                        $panding = '合格';
                    }
                    elseif($vv->res === 2) {
                        $unhege[] = substr($vv->bar_code, 6);
                        $panding = '不合格';
                    }
                    else {
                        $weiZhi[] = substr($vv->bar_code, 6);
                        # code...
                        $panding = '-';
                    }
                    $worksheet->getCell($info_pos->res1.$rowIndex)->setValue($panding); //判定
                }

                //有效日期有位置 而且 周期不为0或null
                if($info_pos->valid_date1 !== '' && $info_pos->cycle) {
                    if($vv->res === 1) {
                        //合格
                        $test_time = $vv->test_time; //试验日期
                        $cycle = ($info_pos->cycle)*12; //周期

                        $valid_date = date("Y-m-d", strtotime("+$cycle month", strtotime($test_time)));
                        $first_date = date("Y-m-d", strtotime("first day of +$cycle month", strtotime($test_time)));
                        if(substr($valid_date, 0, 7) !== substr($first_date, 0, 7)) {
                            $valid_date = date("Y-m-d", strtotime("last day of +$cycle month", strtotime($test_time)));
                        }

                        $valid_date = date('Y-m-d', strtotime("-1 day", strtotime($valid_date)));
                    }
                    else {
                        # 不合格
                        $valid_date = "—";
                    }

                    $worksheet->getCell($info_pos->valid_date1.$rowIndex)->setValue($valid_date); //有效日期
                }
                //------------------------------------------ 试验条件 start ---------
                $key_condition = DB::table('a_condition_key')->select('id', 'item_id', 'pos_start', 'pos_end', 'std_tool_item_rel_id')->where([
                    ['std_id', '=', $vv->std_id],
                    ['tool_id', '=', $vv->tool_id],
                    ['pos_start', '!=', ''],
                    ['pos_end', '!=', '']
                ])->get();
                foreach($key_condition as $vvv) {
                    $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                    $b = in_array($vvv->item_id, $arr_item_id); //in_array($value, $array);
                    if($b) {
                        # code...
                        $val_condition = DB::table('a_condition')->where('condition_key_id', '=', $vvv->id)->whereIn('vol_class_id', [0, $vv->vol_id, $vv->class_id])->value('val');
                    }
                    else {
                        # code...
                        $val_condition = '/';
                    }
                    $vvvstart = empty($excel_pos_detail) ? $vvv->pos_start : (isset($excel_pos_detail[$vvv->std_tool_item_rel_id]['condition_key'][$vvv->id]['pos_start']) ? $excel_pos_detail[$vvv->std_tool_item_rel_id]['condition_key'][$vvv->id]['pos_start'] : '');
                    $vvvend = empty($excel_pos_detail) ? $vvv->pos_end : (isset($excel_pos_detail[$vvv->std_tool_item_rel_id]['condition_key'][$vvv->id]['pos_end']) ? $excel_pos_detail[$vvv->std_tool_item_rel_id]['condition_key'][$vvv->id]['pos_end'] : '');

                    if($vvvstart && $vvvend) {
                        $worksheet->mergeCells($vvvstart.$rowIndex.':'.$vvvend.$rowIndex); //试验条件  合并单元格
                        $worksheet->getCell($vvvstart.$rowIndex)->setValue($val_condition); //试验条件
                    }
                } //内内层 foreach 结尾


                //------------------------------------------ 试验条件 end ---------

                //------------------------------------------ 单项目的检测结果（符合、不符合） start ---------

                $item_res = DB::table('a_std_tool_item_rel')->select('id', 'testitem_id', 'pos_start', 'pos_end')->where([
                    ['std_id', '=', $vv->std_id],
                    ['tool_id', '=', $vv->tool_id],
                ])->get();
                $item_rel_ids = [];

                foreach($item_res as $vvv) {
                    $item_rel_ids[] = $vvv->id;
                    $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                    $b = in_array($vvv->testitem_id, $arr_item_id); //in_array($value, $array);
                    if($b) {
                        # code...
                        $test_res = DB::table('a_item_data')->where([
                            ['sample_info_id', '=', $vv->id],
                            ['item_id', '=', $vvv->testitem_id]
                        ])->value('res');
                        //结论,0-不符合，1-符合，2-未录入
                        if($test_res === 0) {
                            $test_res = '不符合';
                        }
                        elseif($test_res === 1) {
                            $test_res = '符合';
                        }
                        elseif($test_res === 3) {
                            $test_res = '/';
                        }
                        else {
                            # code...
                            $test_res = '';
                        }
                    }
                    else {
                        # code...
                        $test_res = '/';
                    }

                    $vvvstart = empty($excel_pos_detail) ? $vvv->pos_start : (isset($excel_pos_detail[$vvv->id]['pos_start']) ? $excel_pos_detail[$vvv->id]['pos_start'] : '');
                    $vvvend = empty($excel_pos_detail) ? $vvv->pos_end : (isset($excel_pos_detail[$vvv->id]['pos_end']) ? $excel_pos_detail[$vvv->id]['pos_end'] : '');
                    if($vvvstart && $vvvend) {
                        //                        dump($vvvstart,$vvvend);
                        $worksheet->mergeCells($vvvstart.$rowIndex.':'.$vvvend.$rowIndex); //单项目的检测结果  合并单元格
                        $worksheet->getCell($vvvstart.$rowIndex)->setValue($test_res); //单项目的检测结果
                    }
                } //内内层 foreach 结尾
                //------------------------------------------ 单项目的检测结果（符合、不符合） end   ---------

                //------------------------------------------ 试验数据 start ---------
                $key_data = DB::table('a_testdata_key')->select('id', 'item_id', 'pos_start', 'pos_end', 'std_tool_item_rel_id')->where([
                    ['std_id', '=', $vv->std_id],
                    ['tool_id', '=', $vv->tool_id],
                    ['pos_start', '!=', ''],
                    ['pos_end', '!=', '']
                ])->whereIn('std_tool_item_rel_id', $item_rel_ids)->get();
                
                foreach($key_data as $vvv) {
                    $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                    $b = in_array($vvv->item_id, $arr_item_id); //in_array($value, $array);
                    if($b) {
                        # code...
                        $val_testdata = DB::table('a_input_data')->where([
                            ['sample_info_id', '=', $vv->id],
                            ['testdata_key_id', '=', $vvv->id]
                        ])->value('val');
                        // dd( "{$val_testdata} --- {$vv->id}---- {$vvv->id}");
                    }
                    else {
                        # code...
                        $val_testdata = '/';
                    }
                    try {
                        $vvvstart = empty($excel_pos_detail) ? $vvv->pos_start : $excel_pos_detail[$vvv->std_tool_item_rel_id]['testdata_key'][$vvv->id]['pos_start'];
                        $vvvend = empty($excel_pos_detail) ? $vvv->pos_end : $excel_pos_detail[$vvv->std_tool_item_rel_id]['testdata_key'][$vvv->id]['pos_end'];
                    } catch(\Exception $exception) {
                        $vvvstart = $vvvend = '';
                    }
                    if($vvvstart && $vvvend) {
                        $worksheet->mergeCells($vvvstart.$rowIndex.':'.$vvvend.$rowIndex); //试验数据  合并单元格
                        $worksheet->getCell($vvvstart.$rowIndex)->setValue($val_testdata." "); //试验数据
                    }
                } //内内层 foreach 结尾
                //                if(isAdmin()){
                //                    dd(111);
                //                }
                //------------------------------------------ 试验数据 end ---------
                $rowIndex ++;
            } //内层 foreach 结尾

            //检测结论，检验结论
            $jlcellstr = trim($worksheet->getCell('A'.$rowIndex)->getValue());
            if($jlcellstr === '检测结论' || $jlcellstr === '检验结论') {
                $range = $worksheet->getCell('A'.$rowIndex)->getMergeRange();
                $rangeAry = explode(':', $range);
                $ABCpos = dec2ABC(ABC2decimal(substr($rangeAry[1], 0, 1)) + 1);
                $resStr = '';
                if(count($hege) > 0) {
                    $hege = SerialNumberToStr(SerialNumber($hege), '#');
                    $resStr .= '样品'.$hege.'按'.$v->std_num.'标准检测合格。';
                }
                if(count($unhege) > 0) {
                    $unhege = SerialNumberToStr(SerialNumber($unhege), '#');
                    $resStr .= '样品'.$unhege.'按'.$v->std_num.'标准检测不合格。';
                }
                if(count($weiZhi) > 0) {
                    $weiZhi = SerialNumberToStr(SerialNumber($weiZhi), '#');
                    $resStr .= '样品'.$weiZhi.'检测状态未知。';
                }
                $worksheet->getCell($ABCpos.$rowIndex)->setValue($resStr);
            }
            $this->addRemark($worksheet, $contract_id, $v);

        } //最外层 foreach 结尾

        //================================================== 样品信息 start ============================================
        $template_file = './storage/ExcelTemplate/样品信息.xls'; //考虑到兼容Linux，斜杠用“/”
        $exist_sample_info_excel = file_exists($template_file);
        if($exist_sample_info_excel) {
            $spreadsheet_sam_info = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取模板文件
            $worksheet_sam_info = $spreadsheet_sam_info->getSheet(0); //按索引获取表单，从 0 开始
            $worksheet_sam_info->setTitle('样品信息'); //重命名工作表
            $spreadsheet->addExternalSheet($worksheet_sam_info); //添加外部工作表
            //为了防止内存泄漏，建议用完手动清理
            $spreadsheet_sam_info->disconnectWorksheets();
            unset($spreadsheet_sam_info);

            $worksheet = $spreadsheet->getSheetByName('样品信息'); //按表单名 获取到激活的表单

            $worksheet->getHeaderFooter()->setOddHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(样品信息页眉头)  Odd
            $worksheet->getHeaderFooter()->setEvenHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(样品信息页眉头) Even
            $worksheet->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

            $worksheet->getHeaderFooter()->setOddFooter($footerContent); //页脚  Odd
            $worksheet->getHeaderFooter()->setEvenFooter($footerContent); //页脚  Even
            $worksheet->getPageMargins()->setFooter($footerPageMargins); //设置页脚的页边距

            $worksheet->getPageMargins()->setBottom($bottomPageMargins); //设置下边的页边距

            $data_sample_info = DB::table('a_sample_info as B')->select('B.*', 'A.tool_name', 'A.voltage', 'A.class')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->orderBy('bar_code')->get();
            $rowIndex_sam_info = 3; //样品信息的行号
            foreach($data_sample_info as $kk => $vv) {
                if($kk > 0) {
                    $worksheet->insertNewRowBefore($rowIndex_sam_info, 1); //在第x行之前插入1个新行
                }
                $worksheet_sam_info->getCell("A".$rowIndex_sam_info)->setValue($vv->tool_name); //样品信息---样品名称
                $worksheet_sam_info->getCell("B".$rowIndex_sam_info)->setValue(substr($vv->bar_code, 6).'#'); //样品信息---样品号
                $worksheet_sam_info->getCell("C".$rowIndex_sam_info)->setValue($vv->bar_code); //样品信息---样品编号
                $worksheet_sam_info->getCell("D".$rowIndex_sam_info)->setValue($vv->self_num); //样品信息---样品自编号
                $worksheet_sam_info->getCell("E".$rowIndex_sam_info)->setValue($vv->guige_xinghao); //样品信息---规格型号
                $worksheet_sam_info->getCell("F".$rowIndex_sam_info)->setValue($vv->voltage); //样品信息---额定电压
                $worksheet_sam_info->getCell("G".$rowIndex_sam_info)->setValue($vv->sample_status); //样品信息---样品状态
                $worksheet_sam_info->getCell("H".$rowIndex_sam_info)->setValue($vv->brand); //样品信息---商标
                $worksheet_sam_info->getCell("I".$rowIndex_sam_info)->setValue($vv->manufacturer); //样品信息---生产厂家
                $worksheet_sam_info->getCell("J".$rowIndex_sam_info)->setValue($vv->production_date); //样品信息---生产日期

                $rowIndex_sam_info ++;
            }
        }
        //================================================== 样品信息 end ============================================

        //================================================== 封底2 start ============================================
        // $template_file = './storage/ExcelTemplate/封底2.xls'; //考虑到兼容Linux，斜杠用“/”
        // if(file_exists($template_file)) {
        //     $spreadsheet_common = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取模板文件
        //     $worksheet_common = $spreadsheet_common->getSheet(0); //按索引获取表单，从 0 开始
        //     $worksheet_common->setTitle('封底2'); //重命名工作表
        //     $headerContentFD = "&R{$company_name}     {$report_num_pre}{$data_batch->batchNum}"; //眉头内容
        //     $footerContentFD = '&R&12第 &P 页，共 &N 页'; //页脚内容

        //     $worksheet_common->getHeaderFooter()->setOddHeader($headerContentFD); //检测公司名称 + 报告编号前缀 + 报告编号(封底页眉头)  Odd
        //     $worksheet_common->getHeaderFooter()->setEvenHeader($headerContentFD); //检测公司名称 + 报告编号前缀 + 报告编号(封底页眉头) Even
        //     $worksheet_common->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

        //     $worksheet_common->getHeaderFooter()->setOddFooter($footerContentFD); //页脚  Odd
        //     $worksheet_common->getHeaderFooter()->setEvenFooter($footerContentFD); //页脚  Even
        //     $worksheet_common->getPageMargins()->setFooter($footerPageMargins); //设置页脚的页边距

        //     $worksheet_common->getPageMargins()->setBottom($bottomPageMargins); //设置下边的页边距

        //     $spreadsheet->addExternalSheet($worksheet_common); //添加外部工作表
        //     //为了防止内存泄漏，建议用完手动清理
        //     $spreadsheet_common->disconnectWorksheets();
        //     unset($spreadsheet_common);
        // }
        //================================================== 封底2 end ============================================


        // $worksheet = $spreadsheet->getActiveSheet();//获取到激活的表单
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');

        //        if (isset(request()->toPdf) && request()->toPdf == 1) {
        //            @mkdir('reportTemp');
        //            //不能含中文
        //            $savepath = 'reportTemp/' . $data_batch->batchNum . ".xls";
        //            $writer->save($savepath);//将Excel文件保存在服务器的磁盘上
        //            $filepath = getenv('DOCUMENT_ROOT') . '/' . $savepath;
        //            $url = 'http://exceltopdf.hbjc2018.cn?filepath=' . urlencode($filepath);
        //            curl($url);
        //            $pdfpath = 'reportTemp/' . $data_batch->batchNum . ".pdf";
        //            if (file_exists($pdfpath)) {
        //                @unlink($savepath);
        //                header('location:' . request()->root() . '/' . $pdfpath);
        //                //为了防止内存泄露，建议用完手动清理
        //                $spreadsheet->disconnectWorksheets();
        //                unset($spreadsheet);
        //                exit();
        //            }
        //        }
        
        //将xls文件保存,将Excel文件保存在服务器的磁盘上
        if($data_batch->status > 13){
            try{
                $disk->put($storeFile,'');
                $writer->save($disk->path($storeFile));
            }catch(Exception $e){
                $disk->delete($storeFile);
            }
        }

        //----------直接在浏览器下载----start
        $file_name = "检测报告".$data_batch->batchNum.".xls"; //输出的文件名称

        // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出Excel 07文件
        header('Content-Type: application/vnd.ms-excel'); //告诉浏览器输出Excel 03版本文件
        header('Content-Disposition: attachment;filename='.urlencode($file_name)); //告诉浏览器输出的文件名称
        header('Cache-Control: max-age=0'); //禁止缓存

        //----------直接在浏览器下载----end

        /* 生成HTML */
        // $writer = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
        // $writer->writeAllSheets();
        // $writer->save("05featuredemo.htm");
        /* 生成HTML */

        // $writer->setPreCalculateFormulas(false); //禁用公式预先计算
        $writer->save('php://output'); //直接在浏览器下载


        /* //未测试通过
        // $writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Mpdf');
        $writer->setPreCalculateFormulas(false);//禁用公式预先计算
        $writer->save("05featuredemo.pdf"); */

        //为了防止内存泄露，建议用完手动清理
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
    }

    private function addRemark($worksheet, $contract_id, $v) {
        /*添加备注*/
        $remarkinfo = DB::table('a_sample_info as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->select('B.remark', 'B.bar_code')->orderBy('B.bar_code')->get();
        $count = count($remarkinfo);
        $remarksBe = [];
        for($i = 0; $i < $count; $i ++) {
            $mark = $remarkinfo[$i]->remark;
            if($mark) {
                $remarksBe[$mark][] = substr($remarkinfo[$i]->bar_code, 6);
            }
        }
        $remarks = [];
        foreach($remarksBe as $mark => $barcodes) {
            $num = SerialNumber($barcodes);
            $num = SerialNumberToStr($num, '#');
            $remarks[] = $num.$mark;
        }
        if(!empty($remarks)) {
            $newRemark = '';
            foreach($remarks as $k => $remark) {
                if($remark) {
                    $newRemark .= "▶ ".$remark.PHP_EOL;
                }
            }
            $highestrow = $worksheet->getHighestRow();
            $lastrowAval = $worksheet->getCell('A'.$highestrow)->getValue();
            while($lastrowAval === null) {
                $highestrow -= 1;
                $lastrowAval = $worksheet->getCell('A'.$highestrow)->getValue();
            }
            if(trim($lastrowAval) !== '备注') {
                $highestrow += 1;
                $worksheet->insertNewRowBefore($highestrow);
                $worksheet->mergeCells('A'.$highestrow.':C'.$highestrow);
                $highestColumn = $worksheet->getHighestColumn();
                $worksheet->mergeCells('D'.$highestrow.':'.$highestColumn.$highestrow);
                $worksheet->setCellValue('A'.$highestrow, '备注');
            }
            $mergecells = $worksheet->getMergeCells();  //获取合并的单元格
            $lastmergecellsget = preg_grep("/$highestrow:(.*)$highestrow/", $mergecells); //获取最后一行合并的单元格
            $lastmergecells = array_pop($lastmergecellsget); //获取填写备注的合并单元格
            $remarkcolumn = preg_replace("/:(.*)/", '', $lastmergecells); //获取填写备注的单元格
            $oldRemark = $worksheet->getCell($remarkcolumn)->getValue();  //备注旧值
            $remark = $oldRemark ? $oldRemark.PHP_EOL.$newRemark : $newRemark;
            $worksheet->setCellValue($remarkcolumn, $remark);
            $worksheet->getStyle($remarkcolumn)->getAlignment()->setWrapText(true);
            $height = $worksheet->getRowDimension($highestrow)->getRowHeight();
            $height += 13*count($remarks);
            $worksheet->getRowDimension($highestrow)->setRowHeight($height);
        }
    }

    //获取第一个样品的表格信息（合并信息及数据）
    private function sampleMergeCells($worksheet,$info_pos){
        $mergeCells = $worksheet->getMergeCells();
        $initRowIndex = $info_pos->line_num;
        $intRowIndexMax = $initRowIndex + $info_pos->sample_rowspann - 1;
        $toMerge = [];
        foreach($mergeCells as $cells){
            $cell = explode(':',$cells)[0];
            $len = strlen($cell);
            for($i = 0; $i < $len; $i++) {
                if(!preg_match('/[A-Za-z]/',substr($cell,$i,1))){
                    $int = substr($cell,$i);
                    if($int >= $initRowIndex && $int <= $intRowIndexMax){
                        $toMerge[] = $cells;
                    }
                    continue;
                }
            }
        }
        return $toMerge;
    }

    /*合并新增加的相关行，并对有数据的行添加内容*/
    private function toMergeCells($kk,$info_pos,$sampleMergeCells,$worksheet){
        $sample_rowspann = $info_pos->sample_rowspann;
        foreach($sampleMergeCells as $cells){
            $cellsAry = explode(':',$cells);
            $startABCint = $this->splitABCint($cellsAry[0]);
            $endABCint = $this->splitABCint($cellsAry[1]);
            $toMergeCells[] = [$startABCint,$endABCint];
            $start = $startABCint[0] . ($startABCint[1] + $kk*$sample_rowspann);
            $end = $endABCint[0] . ($endABCint[1] + $kk*$sample_rowspann);
            $toMerge = "{$start}:{$end}";
            //合并
            $worksheet->mergeCells($toMerge);
        }
    }
    private function splitABCint($str){
        $len = strlen($str);
        for($j = 0; $j < $len; $j++) {
            if(!preg_match('/[A-Za-z]/',substr($str,$j,1))){
                $int = substr($str,$j-1);
                $abc = substr($str,0,$j-1);
            }
        }
        return [$abc,$int];
    }

    private function sampleDefVal($worksheet,$info_pos,$kk,$sampleMergeCells){
        $sample_rowspann = $info_pos->sample_rowspann;
        $initRowIndex = (int)$info_pos->line_num;
        $Amerge = $worksheet->getCell('A1')->getMergeRange();
        $maxMerge = explode(':',$Amerge)[1];
        $maxColumn = $worksheet->getCell($maxMerge)->getColumn();
        $maxRowIndex = $initRowIndex + $sample_rowspann - 1;
        $mergeCells = $sampleMergeCells;

        for($i = 1; $i <= ABC2decimal($maxColumn); $i ++) {
            for($j = $initRowIndex; $j <= $maxRowIndex; $j ++) {
                $col = dec2ABC($i);
                $cell = $col.$j;
                $range = $worksheet->getCell($cell)->getMergeRange();
                if(in_array($range,$mergeCells)){
                    continue;
                }
                if($range){
                    $mergeCells[] = $range;
                }
            }
        }
        foreach($mergeCells as $cells){
            $cell = explode(':',$cells)[0];
            if($def = $worksheet->getCell($cell)->getValue()) {
                $col = $worksheet->getCell($cell)->getColumn();
                $row = substr($cell,strlen($col));
                $cell = $col.($row + $kk*$sample_rowspann);
                $worksheet->getCell($cell)->setValue($def);
            }
        }

    }

}
